package bookManage.Model.dao;

import bookManage.Model.entity.BookShow;
import bookManage.utils.DBUtil;
import bookManage.Model.entity.Book;
import bookManage.Model.entity.BookContent;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @BelongsProject: library-management-system-master
 * @BelongsPackage: bookManage.Model.service
 * @Author: yuan wang
 * @CreateTime: 2023-02-01  08:59
 * @Description: Dao接口的实现类
 * @Version: 1.0
 */
public class DaoImpl implements Dao {
    @Override
    public int addBook(Book book) throws SQLException {
        Connection connection;

        connection = DBUtil.getConnection();
        String sql = "insert into book (bookName, writer, kind, number, publish, bookIntroduction, writerIntroduction, picture, status, borrowNumber) values (?,?,?,?,?,?,?,?,?,?);";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, book.getBookName());
        preparedStatement.setObject(2, book.getWriter());
        preparedStatement.setObject(3, book.getKind());
        preparedStatement.setObject(4, book.getNumber());
        preparedStatement.setObject(5, book.getPublish());
        preparedStatement.setObject(6, book.getBookIntroduction());
        preparedStatement.setObject(7, book.getWriterIntroduction());
        preparedStatement.setObject(8, book.getPicture());
        preparedStatement.setObject(9, "在馆");
        preparedStatement.setObject(10, 0);
        int i = preparedStatement.executeUpdate();

        preparedStatement.close();
        return i;
    }

    @Override
    public int delBook(int id) throws SQLException {
        Connection connection;

        connection = DBUtil.getConnection();
        String sql = "delete from book where id = ? and status = '在馆';";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, id);
        int i = preparedStatement.executeUpdate();

        preparedStatement.close();
        return i;
    }

    @Override
    public int countBook(String number, String status) throws SQLException {
        Connection connection;
        int i = 0;

        if (status.equals("所有")) {
            connection = DBUtil.getConnection();
            String sql = "select * from book where number = ?;";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, number);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                i++;
            }
        } else {
            connection = DBUtil.getConnection();
            String sql = "select * from book where number = ? and status = ?;";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, number);
            preparedStatement.setObject(2, status);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                i++;
            }

            resultSet.close();
            preparedStatement.close();
        }

        return i;
    }

    @Override
    public int editBook(Book book, String oldNumber) throws SQLException {
        Connection connection;

        connection = DBUtil.getConnection();
        String sql = "update book set bookName = ?, writer = ?, kind = ?, number = ?, publish = ?, bookIntroduction = ?, writerIntroduction = ?, picture = ? where number = ?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, book.getBookName());
        preparedStatement.setObject(2, book.getWriter());
        preparedStatement.setObject(3, book.getKind());
        preparedStatement.setObject(4, book.getNumber());
        preparedStatement.setObject(5, book.getPublish());
        preparedStatement.setObject(6, book.getBookIntroduction());
        preparedStatement.setObject(7, book.getWriterIntroduction());
        preparedStatement.setObject(8, book.getPicture());
        preparedStatement.setObject(9, oldNumber);
        int i = preparedStatement.executeUpdate();

        preparedStatement.close();
        return i;
    }

    @Override
    public int borrowNumber(String number) throws SQLException {
        Connection connection;

        int borrowNumber = 0;

        connection = DBUtil.getConnection();
        String sql = "select borrowNumber from book where number = ?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, number);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            borrowNumber += resultSet.getInt(1);
        }

        resultSet.close();
        preparedStatement.close();
        return borrowNumber;
    }

    @Override
    public List<BookShow> queryBook(String choice) throws SQLException {
        Connection connection;

        List<BookShow> bookShowList = new ArrayList<>();

        ResultSet resultSet;
        PreparedStatement preparedStatement;

        connection = DBUtil.getConnection();
        String sql;
        if (Objects.equals(choice, "逾期")) {
            sql = "select distinct b.bookName, b.writer, b.kind, b.number, b.publish, b.picture from book b, record r where b.number = r.number and DATEDIFF(NOW(), r.borrowTime) > 30  order by bookName;";
            preparedStatement = connection.prepareStatement(sql);
        } else if (choice == null || choice.equals("") || choice.equals("所有")){
            sql = "select distinct bookName, writer, kind, number, publish, picture from book order by bookName;";
            preparedStatement = connection.prepareStatement(sql);
        } else if (choice.equals("借出")){
            sql = "select distinct bookName, writer, kind, number, publish, picture from book where status != ? order by bookName;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, "在馆");
        } else {
            sql = "select distinct bookName, writer, kind, number, publish, picture from book where status = ? order by bookName;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, "在馆");
        }

        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            BookShow bookShow = new BookShow();
            bookShow.setBookName(resultSet.getString(1));
            bookShow.setWriter(resultSet.getString(2));
            bookShow.setKind(resultSet.getString(3));
            bookShow.setNumber(resultSet.getString(4));
            bookShow.setPublish(resultSet.getString(5));
            bookShow.setPicture(resultSet.getString(6));
            bookShow.setCountAll(countBook(bookShow.getNumber(), "所有"));
            bookShow.setCountExist(countBook(bookShow.getNumber(), "在馆") + countBook(bookShow.getNumber(), ""));
            bookShow.setCountLend(bookShow.getCountAll() - bookShow.getCountExist());
            bookShow.setBorrowNumber(borrowNumber(bookShow.getNumber()));
            bookShowList.add(bookShow);
        }

        resultSet.close();
        preparedStatement.close();
        return bookShowList;
    }

    @Override
    public Book queryBookContent(String number) throws SQLException {
        Connection connection;

        Book book = new Book();

        connection = DBUtil.getConnection();
        String sql = "select bookName, writer, kind, number, publish, bookIntroduction, writerIntroduction, picture from book where number = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, number);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            book.setBookName(resultSet.getString(1));
            book.setWriter(resultSet.getString(2));
            book.setKind(resultSet.getString(3));
            book.setNumber(resultSet.getString(4));
            book.setPublish(resultSet.getString(5));
            book.setBookIntroduction(resultSet.getString(6));
            book.setWriterIntroduction(resultSet.getString(7));
            book.setPicture(resultSet.getString(8));
            book.setCountAll(countBook(book.getNumber(), "所有"));
            book.setCountExist(countBook(book.getNumber(), "在馆") + countBook(book.getNumber(), ""));
            book.setCountLend(book.getCountAll() - book.getCountExist());
            book.setBorrowNumber(borrowNumber(book.getNumber()));
        }

        resultSet.close();
        preparedStatement.close();
        return book;
    }

    public List<BookContent> queryBookContentList(String number) throws SQLException {
        Connection connection;

        connection = DBUtil.getConnection();
        List<BookContent> bookContentList = new ArrayList<>();

        String sql = """
                select b.id, b.bookName, b.status, r.username, r.borrowTime, r.returnTime
                from book b
                         left outer join record r on r.bookId = b.id
                where b.number = ?;""";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, number);
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            BookContent bookContent = new BookContent();
            bookContent.setId(resultSet.getInt(1));
            bookContent.setBookName(resultSet.getString(2));
            bookContent.setStatus(resultSet.getString(3));
            if (!Objects.equals(bookContent.getStatus(), "在馆")) {
                bookContent.setBorrower(resultSet.getString(4));
                bookContent.setBorrowTime(resultSet.getString(5));
                bookContent.setReturnTime(resultSet.getString(6));
            }
            bookContentList.add(bookContent);
        }

        resultSet.close();
        preparedStatement.close();
        return bookContentList;
    }

    @Override
    public List<BookShow> dimQueryBook(String bookName) throws SQLException {
        Connection connection;

        connection = DBUtil.getConnection();
        List<BookShow> bookShowList = new ArrayList<>();

        String sql = "select distinct bookName, writer, kind, number, publish, picture from book where bookName like ? order by bookName;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, "%" + bookName + "%");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            BookShow bookShow = new BookShow();
            bookShow.setBookName(resultSet.getString(1));
            bookShow.setWriter(resultSet.getString(2));
            bookShow.setKind(resultSet.getString(3));
            bookShow.setNumber(resultSet.getString(4));
            bookShow.setPublish(resultSet.getString(5));
            bookShow.setPicture(resultSet.getString(6));
            bookShow.setCountAll(countBook(bookShow.getNumber(), "所有"));
            bookShow.setCountExist(countBook(bookShow.getNumber(), "在馆") + countBook(bookShow.getNumber(), ""));
            bookShow.setCountLend(bookShow.getCountAll() - bookShow.getCountExist());
            bookShow.setBorrowNumber(borrowNumber(bookShow.getNumber()));
            bookShowList.add(bookShow);
        }

        resultSet.close();
        preparedStatement.close();
        return bookShowList;
    }
}
